Imports System.Diagnostics.Process
Imports System.Threading
Public Class F_Training_Repors
    Dim WithEvents SBO_Application As SAPbouiCOM.Application
    Dim Ocompany As SAPbobsCOM.Company
    Sub New(ByVal ocompany1 As SAPbobsCOM.Company, ByVal sbo_application1 As SAPbouiCOM.Application)
        SBO_Application = sbo_application1
        Ocompany = ocompany1
    End Sub
    Public Sub Training_Report(ByVal oform As SAPbouiCOM.Form)
        Try
            CFL_BP_PetMaster(oform, SBO_Application)
            oform.DataSources.UserDataSources.Add("oedit1", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("oedit2", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("oedit3", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("oedit4", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("oedit5", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("V_0", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("V_1", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("V_2", SAPbouiCOM.BoDataType.dt_SHORT_TEXT)
            oform.DataSources.UserDataSources.Add("oedit6", SAPbouiCOM.BoDataType.dt_DATE)
            oform.DataSources.UserDataSources.Add("oedit7", SAPbouiCOM.BoDataType.dt_DATE)
            oMatrix = oform.Items.Item("1000001").Specific
            oColumns = oMatrix.Columns
            oColumn = oColumns.Item("V_0")
            oColumn.DataBind.SetBound(True, "", "V_0")
            oColumn = oColumns.Item("V_1")
            oColumn.DataBind.SetBound(True, "", "V_1")
            oColumn = oColumns.Item("V_2")
            oColumn.DataBind.SetBound(True, "", "V_2")
            oform.DataSources.DataTables.Add("OWHS")
            oEdit = oform.Items.Item("4").Specific
            oEdit.DataBind.SetBound(True, "", "oedit1")
            oEdit.ChooseFromListUID = "CFLPM"
            oEdit.ChooseFromListAlias = "U_PetCode"
            oCombo = oform.Items.Item("8").Specific
            oCombo.DataBind.SetBound(True, "", "oedit3")
            oCombo.ValidValues.Add("TAgility", "Training -  Agility")
            oCombo.ValidValues.Add("TObedien", "Training -  Obedience")
            oCombo.Select("TAgility", SAPbouiCOM.BoSearchKey.psk_ByDescription)
            oEdit = oform.Items.Item("9").Specific
            oEdit.DataBind.SetBound(True, "", "oedit6")
            oEdit = oform.Items.Item("11").Specific
            oEdit.DataBind.SetBound(True, "", "oedit7")



        Catch ex As Exception
            SBO_Application.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
        End Try

    End Sub

    Private Sub MatrixLoad(ByVal Level As String)
        Dim sqlstr As String = "SELECT T0.[Code], T0.[Name],'Y' as 'Y'  FROM [dbo].[@AI_TRAINING_LEVER]  T0 WHERE T0.[U_TType] = '" & Level & "'"

        oForm.DataSources.DataTables.Item("OWHS").ExecuteQuery(sqlstr)
        oMatrix = oForm.Items.Item("1000001").Specific
        oMatrix.Clear()
        oMatrix.SelectionMode = SAPbouiCOM.BoMatrixSelect.ms_None
        oColumns = oMatrix.Columns
        oForm.Items.Item("1000001").Specific.Columns.item("V_1").DataBind.Bind("OWHS", "Code")
        oForm.Items.Item("1000001").Specific.Columns.item("V_0").DataBind.Bind("OWHS", "Name")
        oForm.Items.Item("1000001").Specific.Columns.item("V_2").DataBind.Bind("OWHS", "Y")
        oForm.Items.Item("1000001").Specific.Clear()
        oForm.Items.Item("1000001").Specific.LoadFromDataSource()
        oForm.Items.Item("1000001").Specific.AutoResizeColumns()

    End Sub
    Private Sub ComboLoad_Level(ByRef Oform As SAPbouiCOM.Form, ByRef oCombo As SAPbouiCOM.ComboBox, ByVal Level As String)
        Try
            oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRecordSet.DoQuery("SELECT T0.[Code], T0.[Name] FROM [dbo].[@AI_TRAINING_LEVER]  T0 WHERE T0.[U_TType] = '" & Level & "'")
            Dim it As Integer = 1
            For it = oCombo.ValidValues.Count - 1 To 0 Step -1
                oCombo.ValidValues.Remove(it, SAPbouiCOM.BoSearchKey.psk_Index)
            Next
            Do While Not oRecordSet.EoF
                oCombo.ValidValues.Add(oRecordSet.Fields.Item(0).Value, oRecordSet.Fields.Item(1).Value)
                oRecordSet.MoveNext()
            Loop
            'If oRecordSet.RecordCount > 0 Then
            '    oRecordSet.MoveFirst()
            '    oCombo.Select(oRecordSet.Fields.Item(0).Value, SAPbouiCOM.BoSearchKey.psk_ByValue)
            'End If
            oRecordSet = Nothing
            GC.Collect()
        Catch ex As Exception
            'SBO_Application.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
        End Try
    End Sub

    Private Sub SBO_Application_ItemEvent(ByVal FormUID As String, ByRef pVal As SAPbouiCOM.ItemEvent, ByRef BubbleEvent As Boolean) Handles SBO_Application.ItemEvent
        Try
            If pVal.FormUID = "AI_TRI_REP" Then
                oForm = SBO_Application.Forms.Item("AI_TRI_REP")

                If pVal.EventType = SAPbouiCOM.BoEventTypes.et_CHOOSE_FROM_LIST Then
                    Dim oCFLEvento As SAPbouiCOM.IChooseFromListEvent
                    oCFLEvento = pVal
                    Dim sCFL_ID As String
                    sCFL_ID = oCFLEvento.ChooseFromListUID
                    oForm = SBO_Application.Forms.Item(FormUID)
                    Dim oCFL As SAPbouiCOM.ChooseFromList
                    oCFL = oForm.ChooseFromLists.Item(sCFL_ID)
                    Try
                        If oCFLEvento.BeforeAction = False Then
                            Dim oDataTable As SAPbouiCOM.DataTable
                            oDataTable = oCFLEvento.SelectedObjects
                            If pVal.ItemUID = "4" Then
                                Try
                                    oEdit = oForm.Items.Item("4").Specific
                                    oEdit.String = oDataTable.GetValue("U_PetCode", 0)
                                Catch ex As Exception
                                End Try
                            End If
                            If pVal.ItemUID = "6" Then
                                Try
                                    oEdit = oForm.Items.Item("6").Specific
                                    oEdit.String = oDataTable.GetValue("U_PetCode", 0)
                                Catch ex As Exception
                                End Try

                            End If
                        End If
                    Catch ex As Exception
                        '' SBO_Application.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
                    End Try
                End If

                If pVal.ItemUID = "8" And pVal.Before_Action = False And pVal.InnerEvent = False And pVal.EventType = SAPbouiCOM.BoEventTypes.et_COMBO_SELECT Then
                    oCombo = oForm.Items.Item("8").Specific
                    If oCombo.Selected.Value = "TAgility" Then

                        MatrixLoad("Agility")
                    ElseIf oCombo.Selected.Value = "TObedien" Then

                        MatrixLoad("Obedi")
                    Else
                        MatrixLoad("")
                    End If
                End If
                If pVal.ItemUID = "Print" And pVal.Before_Action = False And pVal.InnerEvent = False And pVal.EventType = SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED Then
                    'puni
                    'Training_Report()
                    Dim trd As Threading.Thread
                    trd = New Threading.Thread(AddressOf Training_Report)
                    trd.IsBackground = True
                    trd.SetApartmentState(ApartmentState.STA)
                    trd.Start()
                End If

            End If


        Catch ex As Exception

        End Try
    End Sub
    Public Sub Training_report()
        oForm = SBO_Application.Forms.Item("AI_TRI_REP")
        Dim FPet As String = ""
        Dim TPet As String = ""
        Dim ActCode As String = ""
        Dim Level As String = ""
        Try
            oCombo = oForm.Items.Item("8").Specific
            ActCode = oCombo.Selected.Value
        Catch ex As Exception

        End Try

        ' Level = ""
        Dim J As Integer
        For J = 1 To oMatrix.RowCount
            oCheck = oMatrix.Columns.Item("V_2").Cells.Item(J).Specific
            If oCheck.Checked = True Then
                oEdit = oMatrix.Columns.Item("V_1").Cells.Item(J).Specific
                If Level = "" Then
                    Level = "'" & oEdit.String & "'"
                Else
                    Level = "'" & oEdit.String & "'" & "," & Level
                End If


            End If
        Next
        Dim Fdt As String = "2001-01-01"
        Dim TDt As String = "2014-12-31"
        oEdit = oForm.Items.Item("9").Specific
        Dim yr As String
        If oEdit.String = "" Then
            yr = "2000"
            Fdt = yr & "-01-01"
        Else
            Fdt = oEdit.Value
        End If

        oEdit = oForm.Items.Item("11").Specific
        If oEdit.String = "" Then
            yr = Now.Year.ToString
            TDt = yr & "-12-31"
        Else
            TDt = oEdit.Value
        End If
        oEdit = oForm.Items.Item("4").Specific
        If oEdit.String = "" Then
            FPet = "%"
        Else
            FPet = oEdit.String
        End If

        'SELECT Sum(cast(T0.[U_AI_Grade] as int))   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_ProgCode] =[%1] and  T0.[U_AI_Level] =[%0]
        'SELECT count(*)   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_ProgCode] =[%1] and  T0.[U_AI_Level] =[%0]
        Dim sum As Integer = 0
        Dim count As Integer = 0
        Dim Medi As Integer = 0
        Dim Avg As Decimal = 0.0
        Try
            oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim str As String = "SELECT Sum(cast(T0.[U_AI_Grade] as int)) as SumGr   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE  T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ") and T0.[U_AI_Date] between '" & Fdt & "' and '" & TDt & "' "
            oRecordSet.DoQuery("SELECT Sum(cast(T0.[U_AI_Grade] as int)) as SumGr   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ") and T0.[U_AI_Date] between '" & Fdt & "' and '" & TDt & "'")
            sum = oRecordSet.Fields.Item(0).Value
            oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRecordSet.DoQuery("SELECT count(*) as count1   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ")  and T0.[U_AI_Date] between '" & Fdt & "' and '" & TDt & "'")
            count = oRecordSet.Fields.Item(0).Value
            Try
                Avg = sum / count
            Catch ex As Exception
                Avg = 0
            End Try

            oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRecordSet.DoQuery("SELECT count(*) as count1 , T0.[U_AI_Grade]  FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ") and T0.[U_AI_Date] between '" & Fdt & "' and '" & TDt & "' group by  T0.[U_AI_Grade]  order by count1 desc")
            oRecordSet.MoveFirst()
            count = oRecordSet.Fields.Item(0).Value
            If count > 1 Then
                ' Medi = oRecordSet.Fields.Item(1).Value
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Try
        '    oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        '    Dim str As String = "SELECT Sum(cast(T0.[U_AI_Grade] as int)) as SumGr   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE  T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ") "
        '    oRecordSet.DoQuery("SELECT Sum(cast(T0.[U_AI_Grade] as int)) as SumGr   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ")")
        '    sum = oRecordSet.Fields.Item(0).Value
        '    oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        '    oRecordSet.DoQuery("SELECT count(*) as count1   FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ")")
        '    count = oRecordSet.Fields.Item(0).Value
        '    Avg = sum / count
        '    oRecordSet = Ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        '    oRecordSet.DoQuery("SELECT count(*) as count1 , T0.[U_AI_Grade]  FROM [dbo].[@AI_ACTIVITY]  T0 WHERE   T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ")  group by  T0.[U_AI_Grade]  order by count1 desc")
        '    oRecordSet.MoveFirst()
        '    count = oRecordSet.Fields.Item(0).Value
        '    If count > 1 Then
        '        Medi = oRecordSet.Fields.Item(1).Value
        '    End If
        'Catch ex As Exception
        'End Try
        Try
            Dim sqlstr As String
            SBO_Application.StatusBar.SetText("Retrieving Data", SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Success)
            sqlstr = "SELECT '" & Avg & "' as Avg ,'" & Medi & "' as Medi,T0.[U_AI_Handler],T0.[U_AI_ProgName], T0.[U_AI_PetCode], T0.[U_AI_PetName], T0.[U_AI_Date], (SELECT T1.[Name] FROM [dbo].[@AI_TRAINING_LEVER]  T1 WHERE T1.[Code] =T0.[U_AI_Level]) as U_AI_Level,(SELECT T1.[Name] FROM [dbo].[@AI_TRAININGSUBLEVEL]  T1 WHERE T1.[Code] =T0.[U_AI_Training]) as U_AI_Training, T0.[U_AI_Grade] FROM [dbo].[@AI_ACTIVITY]  T0 WHERE T0.[U_AI_PetCode]  like '" & FPet & "' and (T0.[U_AI_Grade] <> '' or T0.[U_AI_Grade]  is null) and T0.[U_AI_ProgCode] ='" & ActCode & "' and  T0.[U_AI_Level] in (" & Level & ") and T0.[U_AI_Date] between '" & Fdt & "' and '" & TDt & "' order by T0.[U_AI_Date] ,T0.[U_AI_ProgName],T0.[U_AI_PetName]"
            Dim frm As MY_Report
            frm = New MY_Report
            frm.Training_Report(sqlstr, Ocompany)
            frm.Text = "Training Report"
            frm.TopMost = True
            frm.Activate()
            frm.ShowDialog()

        Catch ex As Exception
            '            MsgBox(ex.Message)
        End Try


    End Sub
End Class
